/* $RCSFile: ModulesDB.cs $
 * Apache license v2 2011 by Christian Surieux
 * $Source: ?/ADNCRM.Core/DAL/ModulesDB.cs $
 * $log$
 * Revision 6 2010/08/06 19:56:28  christian.surieux
 *   Added Log to All cs files
 */

using System;
using System.IO;
using System.Diagnostics;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Data.SqlClient;
using System.Globalization;

using AddonNice.Security;
using AddonNice.Diagnostics;
using AddonNice.Helpers;
using AddonNice.Settings;
using AddonNice.Settings.Cache;

namespace AddonNice.Configuration
{
	/// <summary>
	/// Class that encapsulates all data logic necessary to add/query/delete modules
	/// configuration, layout and security settings values within the Portal database.
	/// </summary>
	public class ModulesDB :	DBDataStore
	{
        
        const string strNoModule					            =   "NO_MODULE";
        
        const string strGetModulesInPage			            =   "dbo.adn_GetModulesInTab";
        const string strAddGeneralModuleDefinitions             =   "dbo.adn_AddGeneralModuleDefinitions";
        const string strAddModule                               =   "dbo.adn_AddModule";
        const string strDeleteModuleToRecycler                  =   "dbo.adn_DeleteModuleToRecycler";
        const string strDeleteModule                            =   "dbo.adn_DeleteModule";
        const string strDeleteModuleDefinition                  =   "dbo.adn_DeleteModuleDefinition";
        const string strFindModulesByGuid                       =   "dbo.adn_FindModulesByGuid";
        const string strGetGeneralModuleDefinitionByName        =   "dbo.adn_GetGeneralModuleDefinitionByName";
        const string strGetModuleDefinitionByGuid               =   "dbo.adn_GetModuleDefinitionByGuid";        
        const string strGetModuleDefinitionByName               =   "dbo.adn_GetModuleDefinitionByName";        
        const string strGetModuleDefinitionsByDefId             =   "dbo.adn_GetModuleDefinitionsByDefId";
        const string strGetModulesSinglePortal                  =   "dbo.adn_GetModulesSinglePortal";        
        const string strGetCurrentModuleDefinitions             =   "dbo.adn_GetCurrentModuleDefinitions";
        const string strGetCurrentModuleDefinitionsByCategory   =   "dbo.adn_GetCurrentModuleDefinitionsByCategory";
        const string strGetCurrentModuleCategories              =   "dbo.adn_GetCurrentModuleCategories";
        const string strGetSingleModuleDefinition               =   "dbo.adn_GetSingleModuleDefinition";
        const string strGetModulesWithDefIDInTab                =   "dbo.adn_GetModulesWithDefIDInTab";
        const string strGetModulesByDefId                       =   "dbo.adn_GetModulesByDefId";
        const string strGetModuleDefinitionsLock                =   "dbo.adn_GetModuleDefinitionsLock";
        const string strSwitchModuleDefinitionsLock             =   "dbo.adn_SwitchModuleDefinitionsLock";
        const string strGetModuleDefinitions                    =   "dbo.adn_GetModuleDefinitions";
        const string strUpdateGeneralModuleDefinitions          =   "dbo.adn_UpdateGeneralModuleDefinitions";
        const string strGetGuid                                 =   "dbo.adn_GetGuid";
        const string strGetModuleDefinitionByID                 =   "dbo.adn_GetModuleDefinitionByID";
        const string strGetModuleInUse                          =   "dbo.adn_GetModuleInUse";
        const string strUpdateModuleDefinitions                 =   "dbo.adn_UpdateModuleDefinitions";
        const string strGetModuleConfigurationForIndividualModule   =   "dbo.adn_GetModuleConfigurationForIndividualModule";
        const string strUpdateModule                            =   "dbo.adn_UpdateModule";
        const string strUpdateModuleSettingsID                  =   "dbo.adn_UpdateModuleSettingsID";
        const string strUpdateModuleViewRole                    =   "dbo.adn_UpdateModuleViewRole";
        const string strUpdateModuleOrder                       =   "dbo.adn_UpdateModuleOrder";
        const string strGetPageModules                          =   "dbo.adn_GetPageModules";
        const string strGetModulesAllPortals                    =   "dbo.adn_GetModulesAllPortals";
        const string strGetModulesByName                        =   "dbo.adn_GetModulesByName";
        const string strGetModuleSettings                       =   "dbo.adn_GetModuleSettings";
        const string strGetModuleSettingsGroup                  =   "dbo.adn_GetModuleSettingsGroup";
        const string strGetModuleUserSettings                   =   "dbo.adn_GetModuleUserSettings";
        const string strGetModuleUserSettingsGroup              =   "dbo.adn_GetModuleUserSettingsGroup";
        const string strGetModuleSingleSetting                  =   "dbo.adn_GetModuleSingleSetting";
        const string strUpdateModuleSetting                     =   "dbo.adn_UpdateModuleSetting";
        const string strUpdateModuleUserSetting                 =   "dbo.adn_UpdateModuleUserSetting";

        #region Setting status On/Off for a specified model of modules to allow maintenance

        /// <summary>
        /// The GetModuleDefinitionsLock method returns the lock status of a module type
        /// </summary>
        public bool GetModuleDefinitionsLock(Guid GeneralModDefID)
        {
            SqlCommand myCommand		    =   new SqlCommand(strGetModuleDefinitionsLock, myConnection);
            myCommand.CommandType		    =   CommandType.StoredProcedure;

            SqlParameter parameterModuleID  =   new SqlParameter(StStr.ATGeneralModDefID, SqlDbType.UniqueIdentifier);
            parameterModuleID.Value         =   GeneralModDefID;
            myCommand.Parameters.Add(parameterModuleID);
            
            bool Result                     =   false;
            try
            {
                // Open the database connection and execute the command
                myConnection.Open();
                Result                      =   (bool)myCommand.ExecuteScalar();
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleDefinitionsLock GeneralModDefID: {0}, ex : {1}",GeneralModDefID, exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
            return Result;
        }

        
        /// <summary>
        /// The SwitchModuleDefinitionsLock method switch the lock status of a module model for all portals
        /// The module will not be loaded in new portalsettings is status is of
        /// </summary>
        public void SwitchModuleDefinitionsLock(Guid GeneralModDefID)
        {
            SqlCommand myCommand		    =   new SqlCommand(strSwitchModuleDefinitionsLock, myConnection);
            myCommand.CommandType		    =   CommandType.StoredProcedure;

            SqlParameter parameterModuleID  =   new SqlParameter(StStr.ATGeneralModDefID, SqlDbType.UniqueIdentifier);
            parameterModuleID.Value         =   GeneralModDefID;
            myCommand.Parameters.Add(parameterModuleID);

            try
            {
                // Open the database connection and execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB SwitchModuleDefinitionsLock GeneralModDefID: {0}, ex : {1}",GeneralModDefID, exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
        }

        #endregion Setting status On/Off for a specified model of modules to allow maintenance

        #region Models of modules contained in GeneralModuleDefinitions table


        /// <summary>
        /// Returns all the model of module registered in AddonNice, from GeneralModuleDefinition table
        /// </summary>
        public SqlDataReader GetModuleDefinitions()
        {
            SqlCommand myCommand		=   new SqlCommand(strGetModuleDefinitions, myConnection);
            myCommand.CommandType		=   CommandType.StoredProcedure;

            SqlDataReader dr			=   null;
            try
            {
                myConnection.Open();
                dr                      =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if (dr != null)
                    dr.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleDefinitions ex: {0}", exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            return dr;
        }

        /// <summary>
        /// Return the metadata from the GeneralModuleDefinition Table, , GeneralModDefID key is the GuidId module Property
        /// </summary>
        public SqlDataReader GetSingleModuleDefinition(Guid GeneralModDefID)
        {
            SqlCommand myCommand					=   new SqlCommand(strGetSingleModuleDefinition, myConnection);
            myCommand.CommandType					=   CommandType.StoredProcedure;
            
            SqlParameter parameterGeneralModDefID	=   new SqlParameter(StStr.ATGeneralModDefID, SqlDbType.UniqueIdentifier);
            parameterGeneralModDefID.Value			=   GeneralModDefID;
            myCommand.Parameters.Add(parameterGeneralModDefID);

            SqlDataReader dr						=   null;
            try
            {
                myConnection.Open();
                dr									=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if (dr != null)
                    dr.Close();
                if (myConnection.State == ConnectionState.Open)
                    myConnection.Close();
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetSingleModuleDefinition: {0}, ex: {1}", GeneralModDefID, exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            return dr;
        }

        /// <summary>
        /// AddGeneralModuleDefinitions
        /// </summary>
        public void AddGeneralModuleDefinitions(Guid GeneralModDefID, string FriendlyName, string DesktopSrc, 
            string AssemblyName, string ClassName,string Category, bool Admin,string Usage,int VersionType)
        {
            SqlCommand myCommand					=   new SqlCommand(strAddGeneralModuleDefinitions, myConnection);
            myCommand.CommandType					=   CommandType.StoredProcedure;
            SqlParameter parameterGeneralModDefID	=   new SqlParameter(StStr.ATGeneralModDefID, SqlDbType.UniqueIdentifier);
            parameterGeneralModDefID.Value			=   GeneralModDefID;
            myCommand.Parameters.Add(parameterGeneralModDefID);
            
			SqlParameter parameterFriendlyName		=   new SqlParameter(StStr.ATFriendlyName, SqlDbType.NVarChar, 128);
            parameterFriendlyName.Value				=   FriendlyName;
            myCommand.Parameters.Add(parameterFriendlyName);
            
			SqlParameter parameterDesktopSrc		=   new SqlParameter(StStr.ATDesktopSrc, SqlDbType.NVarChar, 256);
            parameterDesktopSrc.Value				=   DesktopSrc;
            myCommand.Parameters.Add(parameterDesktopSrc);
            
			SqlParameter parameterAssemblyName		=   new SqlParameter(StStr.ATAssemblyName, SqlDbType.VarChar, 50);
            parameterAssemblyName.Value				=   (AssemblyName.Length > 50) ? AssemblyName.Substring(0,50):AssemblyName;
            myCommand.Parameters.Add(parameterAssemblyName);
            
			SqlParameter parameterClassName         =   new SqlParameter(StStr.ATClassName, SqlDbType.NVarChar, 128);
            parameterClassName.Value                =   ( ClassName.Length > 128 ) ? ClassName.Substring(0,128):ClassName;
            myCommand.Parameters.Add(parameterClassName);
            
			SqlParameter parameterCategory          =   new SqlParameter(StStr.ATCategory, SqlDbType.NVarChar, 128);
            parameterCategory.Value                 =   Category;
            myCommand.Parameters.Add(parameterCategory);

			SqlParameter parameterUsage             =   new SqlParameter(StStr.ATUsage, SqlDbType.NVarChar, 1024);
            parameterUsage.Value                    =   ( Usage.Length > 1024 ) ? Usage.Substring(0,1024):Usage;
            myCommand.Parameters.Add(parameterUsage);

            SqlParameter parameterAdmin             =   new SqlParameter(StStr.ATAdmin, SqlDbType.Bit);
            parameterAdmin.Value                    =   Admin;
            myCommand.Parameters.Add(parameterAdmin);
            
            SqlParameter parameterVersionType       =   new SqlParameter(StStr.ATVersionType, SqlDbType.Int);
            parameterVersionType.Value              =   VersionType;
            myCommand.Parameters.Add(parameterVersionType);

            // Open the database connection and execute the command
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB AddGeneralModuleDefinitions: {0}, ex: {1}", GeneralModDefID, exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }
        }

        /// <summary>
        /// Update metadata for a module in GeneralModuleDefinitions table, GeneralModDefID key is the GuidId module Property
        /// </summary>
        public void UpdateGeneralModuleDefinitions(Guid GeneralModDefID, string FriendlyName, string DesktopSrc, 
            string AssemblyName, string ClassName,string Category, bool Admin,string Usage,int VersionType)
        {
            using (SqlCommand myCommand = new SqlCommand(strUpdateGeneralModuleDefinitions, myConnection))
            {
                myCommand.CommandType					=   CommandType.StoredProcedure;
                SqlParameter parameterGeneralModDefID	=   new SqlParameter(StStr.ATGeneralModDefID, SqlDbType.UniqueIdentifier);
                parameterGeneralModDefID.Value			=   GeneralModDefID;
                myCommand.Parameters.Add(parameterGeneralModDefID);
                
                SqlParameter parameterFriendlyName		=   new SqlParameter(StStr.ATFriendlyName, SqlDbType.NVarChar, 128);
                parameterFriendlyName.Value				=   FriendlyName;
                myCommand.Parameters.Add(parameterFriendlyName);
                
                SqlParameter parameterDesktopSrc		=   new SqlParameter(StStr.ATDesktopSrc, SqlDbType.NVarChar, 256);
                parameterDesktopSrc.Value				=   DesktopSrc;
                myCommand.Parameters.Add(parameterDesktopSrc);
                
                SqlParameter parameterAssemblyName		=   new SqlParameter(StStr.ATAssemblyName, SqlDbType.VarChar, 50);
                parameterAssemblyName.Value				=   AssemblyName;
                myCommand.Parameters.Add(parameterAssemblyName);
                
                SqlParameter parameterClassName			=   new SqlParameter(StStr.ATClassName, SqlDbType.NVarChar, 128);
                parameterClassName.Value				=   ClassName;
                myCommand.Parameters.Add(parameterClassName);
			    
                SqlParameter parameterCategory          =   new SqlParameter(StStr.ATCategory, SqlDbType.NVarChar, 128);
                parameterCategory.Value                 =   Category;
                myCommand.Parameters.Add(parameterCategory);

                SqlParameter parameterAdmin				=   new SqlParameter(StStr.ATAdmin, SqlDbType.Bit);
                parameterAdmin.Value					=   Admin;
                myCommand.Parameters.Add(parameterAdmin);

                SqlParameter parameterUsage             =   new SqlParameter(StStr.ATUsage, SqlDbType.NVarChar, 1024);
                parameterUsage.Value                    =   ( Usage.Length > 1024 ) ? Usage.Substring(0,1024):Usage;
                myCommand.Parameters.Add(parameterUsage);
                
                SqlParameter parameterVersionType       =   new SqlParameter(StStr.ATVersionType, SqlDbType.Int);
                parameterVersionType.Value              =   VersionType;
                myCommand.Parameters.Add(parameterVersionType);
                try
                {
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB UpdateGeneralModuleDefinitions: {0}, ex: {1}", GeneralModDefID, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }
            }
        }

        /// <summary>
        /// The DeleteModuleDefinition method deletes the specified 
        /// module type definition from the portal.
        /// </summary>
        public void DeleteModuleDefinition(Guid defID)
        {
            using (SqlCommand myCommand = new SqlCommand(strDeleteModuleDefinition, myConnection))
            {
                myCommand.CommandType					=   CommandType.StoredProcedure;
                SqlParameter parameterModuleDefID		=   new SqlParameter(StStr.ATModuleDefID, SqlDbType.UniqueIdentifier);
                parameterModuleDefID.Value				=   defID;
                myCommand.Parameters.Add(parameterModuleDefID);
                // Open the database connection and execute the command
                try
                {
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB DeleteModuleDefinition: {0}, ex: {1}", defID, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }
            }
        }


        public SqlDataReader GetModuleDefinitionsByDefId(int moduleDefId)
        {
            // To avoid case where a datareader has not already closed the connection ( when calling ModuleConfiguration inside a read loop)
            SqlConnection theConnection     =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection               =   new SqlConnection(Config.ConnectionString);

            SqlCommand myCommand			=   new SqlCommand(strGetModuleDefinitionsByDefId, theConnection);
            myCommand.CommandType			=   CommandType.StoredProcedure;
            SqlParameter parameterModuleID	=   new SqlParameter(StStr.ATModuleDefID, SqlDbType.Int);
            parameterModuleID.Value			=   moduleDefId;
            myCommand.Parameters.Add(parameterModuleID);

            SqlDataReader result			=   null;
            try
            {
                theConnection.Open();
                result                      =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleDefinitionsByDefId ex: {0}", ex),DBTraceSwitch.Sw.Info);
                if ( result != null )
                    result.Close();
                if ( theConnection.State == System.Data.ConnectionState.Open)
                    theConnection.Close();
                throw;
            }
            return result;
        }
        

        /// <summary>
        /// Utility: returns the guid of the models of modules
        /// with FriendlyName matching the moduleName parameter
        /// </summary>
        public Guid GetGeneralModuleDefinitionByName(string moduleName)
        {
            using (SqlCommand myCommand = new SqlCommand(strGetGeneralModuleDefinitionByName, myConnection))
            {
                myCommand.CommandType				=   CommandType.StoredProcedure;
                
                SqlParameter parameterFriendlyName	=   new SqlParameter("@FriendlyName", SqlDbType.NVarChar, 128);
                parameterFriendlyName.Value			=   moduleName;
                myCommand.Parameters.Add(parameterFriendlyName);

                // output
                SqlParameter parameterModuleID		=   new SqlParameter(StStr.ATModuleID, SqlDbType.UniqueIdentifier);
                parameterModuleID.Direction			=   ParameterDirection.Output;
                myCommand.Parameters.Add(parameterModuleID);

                try
                {
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetGeneralModuleDefinitionByName:{0}, ex: {1}", moduleName, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }

                if (parameterModuleID.Value != null && parameterModuleID.Value.ToString().Length != 0)
                {
                    try
                    {
                        return new Guid(parameterModuleID.Value.ToString());
                    }
                    catch (Exception ex)
                    {
                        if ( DBTraceSwitch.Sw.TraceError )
                            Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetGeneralModuleDefinitionByName parameterModuleID.Value: '{0}' seems not a valid GUID, ex: {1}", parameterModuleID.Value, ex),DBTraceSwitch.Sw.Info);
                        throw;
                    }
                }
                const string msg    =   "[E]ModulesDB GetGeneralModuleDefinitionByName null GUID.";
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( msg,DBTraceSwitch.Sw.Info);
                throw new ArgumentException(msg, "GUID");
            }
        }

        /// <summary>
        /// Utility : get from the GeneralModuleDefinitions table the Guid of an instanciated module using its moduleid key
        /// </summary>
        public Guid GetModuleGuid(int moduleID)
        {
			string cacheGuid				=   Key.ModuleSettingsGUID(moduleID);
            if (CurrentCache.Get(cacheGuid) != null)
            {
                if ( SettingsTraceSwitch.Sw.TraceVerbose )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]ModulesDB GetModuleGuid found in cache moduleID: {0}",moduleID),SettingsTraceSwitch.Sw.Info);
                return (Guid)CurrentCache.Get(cacheGuid);
            }

			Guid moduleGuid					    =   Guid.Empty;
            using (SqlCommand myCommand         =   new SqlCommand(strGetGuid, myConnection))
            {
                myCommand.CommandType	        =   CommandType.StoredProcedure;

                AddModuleIDParameters(myCommand,moduleID);

                SqlDataReader dr		        =   null;
                try
                {
                    myConnection.Open();
                    dr					        =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    if (dr.Read() && !dr.IsDBNull(0) )
                        moduleGuid		        =   dr.GetGuid(0);
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleGuid ModuleID: {0}, ex: {1}", moduleID, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (dr != null)
                        dr.Close();
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }
                CurrentCache.Insert(cacheGuid, moduleGuid);
                }
			return moduleGuid;
        }

        /// <summary>
        /// For one instanciated module Get the global meta data information on module : loading path, friendly name, admin, etc.
        /// Also get the ModuleDefID for current portal (which validate module metadata for this portal
        /// But don't get the adn_module data.
        /// </summary>
        /// <param name="ModuleID">ModuleID</param>
        /// <returns>A SqlDataReader</returns>
        public SqlDataReader GetModuleDefinitionByID(int xmoduleID)
        {
            // To avoid case where a datareader has not already closed the connection ( when calling ModuleConfiguration inside a read loop)
            SqlConnection theConnection     =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection               =   new SqlConnection(Config.ConnectionString);

            SqlCommand myCommand			=   new SqlCommand(strGetModuleDefinitionByID, theConnection);
            myCommand.CommandType			=   CommandType.StoredProcedure;
            
            AddModuleIDParameters(myCommand,xmoduleID);

            SqlDataReader result			=   null;
            try
            {
                theConnection.Open();
                result                      =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleDefinitionByID ex: {0}", ex),DBTraceSwitch.Sw.Info);
                if ( result != null )
                    result.Close();
                if ( theConnection.State == System.Data.ConnectionState.Open)
                    theConnection.Close();
                throw;
            }
            return result;
        }
        
        #endregion Models of modules contained in GeneralModuleDefinitions table

        #region Module Presence in each portal: Module metadata is accessible to this portal

        /// <summary>
        /// Returns the moduleId for modules with the corresponding ModuleDefId, they are all in the same portal because defId is generated by portal
        /// </summary>
        public SqlDataReader GetModulesByDefId(int ModDefID)
        {
            SqlCommand myCommand				= new SqlCommand(strGetModulesByDefId, myConnection);
            myCommand.CommandType				= CommandType.StoredProcedure;
            
            SqlParameter parameterPortalID		= new SqlParameter(StStr.ATModuleDefID, SqlDbType.Int, 4);
            parameterPortalID.Value				= ModDefID;
            myCommand.Parameters.Add(parameterPortalID);
            

            SqlDataReader dr					= null;
            try
            {
                // Open the database connection and execute the command
                myConnection.Open();
                dr								= myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if (dr != null)
                    dr.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModulesByDefId ModDefID: {0}, ex : {1}", ModDefID,exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            return dr;
        }

        /// <summary>
        /// Returns the ModuleDefID of module which matches the model identifyed by its Guid for the specified Portal.
        /// relation is one->one or zero if model is not allowed for this portal
        /// </summary>
        public int GetModuleDefinitionByGuid(int portalID, Guid guid)
        {
            SqlConnection theConnection                 =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection		                    =   new SqlConnection(Config.ConnectionString);

            using (SqlCommand myCommand = new SqlCommand(strGetModuleDefinitionByGuid, theConnection))
            {
                myCommand.CommandType					=   CommandType.StoredProcedure;
                
                SqlParameter parameterFriendlyName		=   new SqlParameter(StStr.ATGuid, SqlDbType.UniqueIdentifier);
                parameterFriendlyName.Value				=   guid;
                myCommand.Parameters.Add(parameterFriendlyName);
                
                AddPortalIDParameters(myCommand,portalID);
                
                // Output
                SqlParameter parameterModuleID			=   new SqlParameter(StStr.ATModuleID, SqlDbType.Int, 4);
                parameterModuleID.Direction				=   ParameterDirection.Output;
                myCommand.Parameters.Add(parameterModuleID);
                
                int ret                                 =   -1;
                try
                {
                    theConnection.Open();
                    myCommand.ExecuteNonQuery();
                    ret                                 =   (int)parameterModuleID.Value;
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleDefinitionByGuid Guid:{0}, ex : {1}", guid, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (theConnection.State == System.Data.ConnectionState.Open)
                        theConnection.Close();
                }
                return ret;
            }
        }

        /// <summary>
		/// Returns all the portals where a specified model of module is registered
		/// GetModuleInUse Stored Procedure
		/// </summary>
		public SqlDataReader GetModuleInUse(Guid defID)
		{
			SqlCommand myCommand		= new SqlCommand(strGetModuleInUse, myConnection);
			myCommand.CommandType		= CommandType.StoredProcedure;

            // Beware uses StStr.ATModuleID with a Guid
            SqlParameter parameterdefID = new SqlParameter(StStr.ATModuleID, SqlDbType.UniqueIdentifier);
			parameterdefID.Value		= defID;
			myCommand.Parameters.Add(parameterdefID);
			
            SqlDataReader dr			=	null;
			try
			{
				myConnection.Open();
				dr						= myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if ( dr != null )
					dr.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleInUse:{0}, ex : {1}",defID, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			return dr;
		}

        /// <summary>
        /// The GetCurrentModuleDefinitions method returns all ModuleDefID keys for  model of module validated for this portal
        /// ordered by admin, FriendlyName
        /// </summary>
        public SqlDataReader GetCurrentModuleDefinitions(int portalID)
        {
            SqlCommand myCommand			=   new SqlCommand(strGetCurrentModuleDefinitions, myConnection);
            myCommand.CommandType			=   CommandType.StoredProcedure;
            
            AddPortalIDParameters(myCommand,portalID);

            SqlDataReader dr				=   null;
            try
            {
                // Open the database connection and execute the command
                myConnection.Open();
                dr                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if (dr != null)
                    dr.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetCurrentModuleDefinitions PortalID:{0}, ex : {1}", portalID, exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            return dr;
        }

        public SqlDataReader GetCurrentModuleDefinitionsByCategory(int portalID,string category)
        {
            SqlCommand myCommand			=   new SqlCommand(strGetCurrentModuleDefinitionsByCategory, myConnection);
            myCommand.CommandType			=   CommandType.StoredProcedure;
            
            AddPortalIDParameters(myCommand,portalID);

            SqlParameter parameterCategory	=   new SqlParameter(StStr.ATCategory,SqlDbType.NVarChar,128);
            parameterCategory.Value			=   category;
            myCommand.Parameters.Add(parameterCategory);


            SqlDataReader dr				=   null;
            try
            {
                // Open the database connection and execute the command
                myConnection.Open();
                dr                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if (dr != null)
                    dr.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetCurrentModuleDefinitions PortalID:{0}, ex : {1}", portalID, exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            return dr;
        }

        /// <summary>
        /// The GetCurrentModuleDefinitions method returns all existing categories in DB
        /// </summary>
        public SqlDataReader GetCurrentModuleCategories(int portalID)
        {
            SqlCommand myCommand			= new SqlCommand(strGetCurrentModuleCategories, myConnection);
            myCommand.CommandType			= CommandType.StoredProcedure;
            
            AddPortalIDParameters(myCommand,portalID);

            SqlDataReader dr				= null;
            try
            {
                // Open the database connection and execute the command
                myConnection.Open();
                dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if (dr != null)
                    dr.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetCurrentModuleCategories PortalID:{0}, ex : {1}", portalID, exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            return dr;
        }

        /// <summary>
        /// The GetCurrentModuleDefinitions method returns all existing categories in DB
        /// </summary>
        public DataTable GetCurrentModuleCategoriesTb(int portalID)
        {
			SqlDataAdapter myAdapt		        = new SqlDataAdapter(strGetCurrentModuleCategories, myConnection);
			myAdapt.SelectCommand.CommandType	= CommandType.StoredProcedure;
            
            AddPortalIDParameters(myAdapt.SelectCommand,portalID);

			DataTable myDataTable = new DataTable();
			try
			{
				myAdapt.Fill(myDataTable);
                if (DBTraceSwitch.Sw.TraceVerbose)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]ModulesDB GetCurrentModuleCategoriesTb Count: {0}", myDataTable.Rows.Count),DBTraceSwitch.Sw.Info);
			}
			catch (Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetCurrentModuleCategoriesTb PortalID: {0}, ex : {1}", portalID, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if (myConnection.State == System.Data.ConnectionState.Open)
					myConnection.Close();
			}

            return myDataTable;
        }

        /// <summary>
        /// Add or Remove a Module definition from a portal
        /// GeneralModuleDefinition contains the MeteData, key GeneralModDefID (Guid -- the one ine each Module GuidId property )
        /// And ModuleDefinition simply validation the module for, a portal, key ModuleDefID (int) autogenerated
        /// </summary>
        public void UpdateModuleDefinitions(Guid GeneralModDefID, int portalID, bool ischecked)
        {
            SqlConnection theConnection             =   newConnection;
            SqlCommand myCommand					=   new SqlCommand(strUpdateModuleDefinitions, theConnection);
            myCommand.CommandType					=   CommandType.StoredProcedure;
            
            SqlParameter parameterGeneralModDefID	=   new SqlParameter(StStr.ATGeneralModDefID, SqlDbType.UniqueIdentifier);
            parameterGeneralModDefID.Value			=   GeneralModDefID;
            myCommand.Parameters.Add(parameterGeneralModDefID);
            
            AddPortalIDParameters(myCommand,portalID);

            SqlParameter parameterischecked			=   new SqlParameter("@ischecked", SqlDbType.Bit);
            parameterischecked.Value				=   ischecked;
            myCommand.Parameters.Add(parameterischecked);
            
            try
            {
                theConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB UpdateModuleDefinitions: {0}, ex : {1}", GeneralModDefID, exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if (theConnection.State == System.Data.ConnectionState.Open)
                    theConnection.Close();
            }
        }

        /// <summary>
        /// Utility: returns all the ModuleDefID for the specified Portal
        /// that matches the FriendlyName model module .
        /// </summary>
        public int GetModuleDefinitionByName(int portalID, string moduleName)
        {
            using (SqlCommand myCommand = new SqlCommand(strGetModuleDefinitionByName, myConnection))
            {
                myCommand.CommandType					= CommandType.StoredProcedure;
                SqlParameter parameterFriendlyName		= new SqlParameter(StStr.ATFriendlyName, SqlDbType.NVarChar, 128);
                parameterFriendlyName.Value				= moduleName;
                myCommand.Parameters.Add(parameterFriendlyName);
                
                AddPortalIDParameters(myCommand,portalID);

                // output
                SqlParameter parameterModuleID			= new SqlParameter(StStr.ATModuleID, SqlDbType.Int, 4);
                parameterModuleID.Direction				= ParameterDirection.Output;
                myCommand.Parameters.Add(parameterModuleID);

                try
                {
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleDefinitionByName:{0}, ex : {1}", moduleName, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }

                return (int)parameterModuleID.Value;
            }
        }

        #endregion Module Presence in each portal

        #region Instanciated Module

		/// <summary>
        /// Returns all data for an instanciated module: module configuration, ModuleDefId, model datas (Friendlyname, Guid, etc)
        /// </summary>
        public SqlDataReader GetModuleConfigurationForModule(int ModuleID)
        {
            SqlDataReader result			    =   null;
            SqlConnection theConnection         =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection		            =   new SqlConnection(Config.ConnectionString);

            using (SqlCommand MyCommand = new SqlCommand(strGetModuleConfigurationForIndividualModule, theConnection))
            {
                MyCommand.CommandType			=   CommandType.StoredProcedure;

                AddModuleIDParameters(MyCommand,ModuleID);

                try
                {
                    theConnection.Open();
                    result						=   MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleConfigurationForModule ex : {0}", exp),DBTraceSwitch.Sw.Info);
                    if (result != null)
                        result.Close();
                    if (theConnection.State == System.Data.ConnectionState.Open)
                        theConnection.Close();
                    throw;
                }
            }
            return result;
        }

        /// <summary>
		/// Adds a new Module within the Modules database table.
		/// </summary>
        public int AddModule(int PageID, int moduleOrder,int ModuleSettingsID, string paneName, string title, int moduleDefID, int cacheTime,
            string editRoles, string viewRoles, string addRoles, string deleteRoles, string PropertiesRoles,
            string moveModuleRoles, string deleteModuleRoles,
            bool showEveryWhere, bool supportCollapsable)
        {
            SqlConnection theConnection         =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection		            =   new SqlConnection(Config.ConnectionString);

            using (SqlCommand myCommand = new SqlCommand(strAddModule, theConnection))
            {
                myCommand.CommandType					= CommandType.StoredProcedure;
                
                // output
                SqlParameter parameterModuleID			= new SqlParameter(StStr.ATModuleID, SqlDbType.Int);
                parameterModuleID.Direction				= ParameterDirection.Output;
                myCommand.Parameters.Add(parameterModuleID);
                
                SqlParameter parameterModuleDefinitionID = new SqlParameter(StStr.ATModuleDefID, SqlDbType.Int);
                parameterModuleDefinitionID.Value		= moduleDefID;
                myCommand.Parameters.Add(parameterModuleDefinitionID);
                
                AddPageIDParameters(myCommand,PageID);
                
                SqlParameter parameterModuleOrder		= new SqlParameter(StStr.ATModuleOrder, SqlDbType.Int);
                parameterModuleOrder.Value				= moduleOrder;
                myCommand.Parameters.Add(parameterModuleOrder);
                
                SqlParameter parameterModuleSettingsID  = new SqlParameter(StStr.ATModuleSettingsID, SqlDbType.Int);
                parameterModuleSettingsID.Value			= ModuleSettingsID;
                myCommand.Parameters.Add(parameterModuleSettingsID);

                SqlParameter parameterTitle				= new SqlParameter(StStr.ATModuleTitle, SqlDbType.NVarChar, 256);
                parameterTitle.Value					= title;
                myCommand.Parameters.Add(parameterTitle);
                
                SqlParameter parameterPaneName			= new SqlParameter(StStr.ATPaneName, SqlDbType.NVarChar, 50);
                parameterPaneName.Value					= paneName;
                myCommand.Parameters.Add(parameterPaneName);
                
                SqlParameter parameterCacheTime			= new SqlParameter(StStr.ATCacheTime, SqlDbType.Int, 4);
                parameterCacheTime.Value				= cacheTime;
                myCommand.Parameters.Add(parameterCacheTime);
                
                SqlParameter parameterEditRoles			= new SqlParameter(StStr.ATEditRoles, SqlDbType.NVarChar, 512);
                parameterEditRoles.Value				= editRoles;
                myCommand.Parameters.Add(parameterEditRoles);
                
                SqlParameter parameterViewRoles			= new SqlParameter(StStr.ATViewRoles, SqlDbType.NVarChar, 512);
                parameterViewRoles.Value				= viewRoles;
                myCommand.Parameters.Add(parameterViewRoles);
                
                SqlParameter parameterAddRoles			= new SqlParameter(StStr.ATAddRoles, SqlDbType.NVarChar, 512);
                parameterAddRoles.Value					= addRoles;
                myCommand.Parameters.Add(parameterAddRoles);
                
                SqlParameter parameterDeleteRoles		= new SqlParameter(StStr.ATDeleteRoles, SqlDbType.NVarChar, 512);
                parameterDeleteRoles.Value				= deleteRoles;
                myCommand.Parameters.Add(parameterDeleteRoles);
                
                SqlParameter parameterPropertiesRoles	= new SqlParameter(StStr.ATPropertiesRoles, SqlDbType.NVarChar,512);
                parameterPropertiesRoles.Value			= PropertiesRoles;
                myCommand.Parameters.Add(parameterPropertiesRoles);
                
                SqlParameter parameterMoveModuleRoles	= new SqlParameter(StStr.ATMoveModuleRoles, SqlDbType.NVarChar, 512);
                parameterMoveModuleRoles.Value			= moveModuleRoles;
                myCommand.Parameters.Add(parameterMoveModuleRoles);
                
                SqlParameter parameterDeleteModuleRoles = new SqlParameter(StStr.ATDeleteModuleRoles, SqlDbType.NVarChar, 512);
                parameterDeleteModuleRoles.Value		= deleteModuleRoles;
                myCommand.Parameters.Add(parameterDeleteModuleRoles);
                
                SqlParameter parameterShowEveryWhere	= new SqlParameter(StStr.ATShowEveryWhere, SqlDbType.Bit, 1);
                parameterShowEveryWhere.Value			= showEveryWhere;
                myCommand.Parameters.Add(parameterShowEveryWhere);
                
                SqlParameter parameterSupportCollapsable = new SqlParameter(StStr.ATSupportCollapsable, SqlDbType.Bit, 1);
                parameterSupportCollapsable.Value		= supportCollapsable;
                myCommand.Parameters.Add(parameterSupportCollapsable);
                try
                {
                    theConnection.Open();
                    myCommand.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB AddModule:{0}, ex : {1}", moduleDefID, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (theConnection.State == System.Data.ConnectionState.Open)
                        theConnection.Close();
                }
                return (int)parameterModuleID.Value;
            }
        }


        /// <summary>
        /// The UpdateModule method updates a specified Module within the Modules database table.
        /// ModuleId must exists
        /// </summary>
        public int UpdateModule(int PageID, int moduleID, int moduleOrder,int ModuleSettingsID,string paneName, string title, int cacheTime,
            string editRoles, string viewRoles, string addRoles, string deleteRoles, string PropertiesRoles,
            string moveModuleRoles, string deleteModuleRoles, bool showEveryWhere, bool supportCollapsable)
        {
            using (SqlCommand myCommand = new SqlCommand(strUpdateModule, myConnection))
            {
                myCommand.CommandType					=   CommandType.StoredProcedure;
                
                SqlParameter parameterModuleID          =   AddModuleIDParameters(myCommand,moduleID);
                
                AddPageIDParameters(myCommand,PageID);
                
                SqlParameter parameterModuleOrder		=   new SqlParameter(StStr.ATModuleOrder, SqlDbType.Int, 4);
                parameterModuleOrder.Value				=   moduleOrder;
                myCommand.Parameters.Add(parameterModuleOrder);
                
                SqlParameter parameterModuleSettingsID  =   new SqlParameter(StStr.ATModuleSettingsID, SqlDbType.Int);
                parameterModuleSettingsID.Value			=   ModuleSettingsID;
                myCommand.Parameters.Add(parameterModuleSettingsID);

                SqlParameter parameterTitle				=   new SqlParameter(StStr.ATModuleTitle, SqlDbType.NVarChar, 256);
                parameterTitle.Value					=   title;
                myCommand.Parameters.Add(parameterTitle);
                
                SqlParameter parameterPaneName			=   new SqlParameter(StStr.ATPaneName, SqlDbType.NVarChar, 256);
                parameterPaneName.Value					=   paneName;
                myCommand.Parameters.Add(parameterPaneName);
                
                SqlParameter parameterCacheTime			=   new SqlParameter(StStr.ATCacheTime, SqlDbType.Int, 4);
                parameterCacheTime.Value				=   cacheTime;
                myCommand.Parameters.Add(parameterCacheTime);
                
                SqlParameter parameterEditRoles			=   new SqlParameter(StStr.ATEditRoles, SqlDbType.NVarChar, 256);
                parameterEditRoles.Value				=   editRoles;
                myCommand.Parameters.Add(parameterEditRoles);
                
                SqlParameter parameterViewRoles			=   new SqlParameter(StStr.ATViewRoles, SqlDbType.NVarChar, 256);
                parameterViewRoles.Value				=   viewRoles;
                myCommand.Parameters.Add(parameterViewRoles);
                
                SqlParameter parameterAddRoles			=   new SqlParameter(StStr.ATAddRoles, SqlDbType.NVarChar, 256);
                parameterAddRoles.Value					=   addRoles;
                myCommand.Parameters.Add(parameterAddRoles);
                
                SqlParameter parameterDeleteRoles		=   new SqlParameter(StStr.ATDeleteRoles, SqlDbType.NVarChar, 256);
                parameterDeleteRoles.Value				=   deleteRoles;
                myCommand.Parameters.Add(parameterDeleteRoles);
                
                SqlParameter parameterPropertiesRoles	=   new SqlParameter(StStr.ATPropertiesRoles, SqlDbType.NVarChar, 256);
                parameterPropertiesRoles.Value			=   PropertiesRoles;
                myCommand.Parameters.Add(parameterPropertiesRoles);
                
                SqlParameter parameterMoveModuleRoles	=   new SqlParameter(StStr.ATMoveModuleRoles, SqlDbType.NVarChar, 256);
                parameterMoveModuleRoles.Value			=   moveModuleRoles;
                myCommand.Parameters.Add(parameterMoveModuleRoles);
                
                SqlParameter parameterDeleteModuleRoles =   new SqlParameter(StStr.ATDeleteModuleRoles, SqlDbType.NVarChar, 256);
                parameterDeleteModuleRoles.Value		=   deleteModuleRoles;
                myCommand.Parameters.Add(parameterDeleteModuleRoles);
                
                SqlParameter parameterShowEveryWhere	=   new SqlParameter(StStr.ATShowEveryWhere, SqlDbType.Bit, 1);
                parameterShowEveryWhere.Value			=   showEveryWhere;
                myCommand.Parameters.Add(parameterShowEveryWhere);
                
                SqlParameter parameterSupportCollapsable =  new SqlParameter(StStr.ATSupportCollapsable, SqlDbType.Bit, 1);
                parameterSupportCollapsable.Value		=   supportCollapsable;
                myCommand.Parameters.Add(parameterSupportCollapsable);
                
                try
                {
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
					// clear cache
					CurrentCache.Remove(Key.ModuleSettings(moduleID));
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB UpdateModule:{0}, ex : {1}", moduleID, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }
                return (int)parameterModuleID.Value;
            }
        }

        /// <summary>
        /// Changes the settingID for an existing moduleID
        /// </summary>
        public void UpdateModuleSettingsID(int moduleID,int ModuleSettingsID)
        {
            using (SqlCommand myCommand = new SqlCommand(strUpdateModuleSettingsID, myConnection))
            {
                myCommand.CommandType					= CommandType.StoredProcedure;
                
                AddModuleIDParameters(myCommand,moduleID);
                
                SqlParameter parameterModuleSettingsID  = new SqlParameter(StStr.ATModuleSettingsID, SqlDbType.Int);
                parameterModuleSettingsID.Value			= ModuleSettingsID;
                myCommand.Parameters.Add(parameterModuleSettingsID);

                
                try
                {
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
					// clear cache
					CurrentCache.Remove(Key.ModuleSettings(moduleID));
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB UpdateModuleSettingsID ModuleID: {0}, ex: {1}", moduleID, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }
            }
        }

		/// <summary>
        /// The DeleteModule method deletes a specified Module from the Modules database table.
        /// </summary>
        public void DeleteModule(int moduleID,Guid modGuid)
        {
            PortalSettings portalSettings   = (PortalSettings)HttpContext.Current.Items[StStr.strPortalSettings];
            bool useRecycler                = portalSettings.CustomSettings[StStr.SiteSettingsUseRecycler].ToBool();
            
            // The AddModule module doesn't go in recycler
            if ( useRecycler && modGuid == new Guid(StStr.AddModuleGuid) )
                useRecycler =   false;

            using (SqlCommand myCommand = new SqlCommand((useRecycler ? strDeleteModuleToRecycler : strDeleteModule), myConnection))
            {
                myCommand.CommandType			= CommandType.StoredProcedure;
                
                AddModuleIDParameters(myCommand,moduleID);

                if (useRecycler)  //Recycler needs some extra params for entry
                {
                    // Add Recycler-specific Parameters to SPROC
                    SqlParameter paramDeletedBy		= new SqlParameter("@DeletedBy", SqlDbType.NVarChar, 250);
                    paramDeletedBy.Value			= PortalSettings.CurrentIdentity.Name;// CS 20/1/6  MailHelper.GetCurrentUserEmailAddress();
                    myCommand.Parameters.Add(paramDeletedBy);

                    SqlParameter paramDeletedDate	= new SqlParameter("@DateDeleted", SqlDbType.DateTime, 8);
                    paramDeletedDate.Value			= DateTime.Now;
                    myCommand.Parameters.Add(paramDeletedDate);
                }

                try
                {
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB DeleteModule:{0}, ex : {1}", moduleID, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }
            }
        }

        /// <summary>
        /// The UpdateModuleViewRole method updates a specified Module ViewRoles.
        /// </summary>
        public void UpdateModuleViewRole(int moduleID, string viewRoles)
        {
            using (SqlCommand myCommand = new SqlCommand(strUpdateModuleViewRole, myConnection))
            {
                myCommand.CommandType					= CommandType.StoredProcedure;

                AddModuleIDParameters(myCommand,moduleID);
                
                SqlParameter parameterViewRoles			= new SqlParameter(StStr.ATViewRoles, SqlDbType.NVarChar, 256);
                parameterViewRoles.Value				= viewRoles;
                myCommand.Parameters.Add(parameterViewRoles);

                try
                {
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
					CurrentCache.Remove(Key.ModuleSettings(moduleID));
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB UpdateModuleViewRole:{0}, ex : {1}", moduleID, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }
            }
        }


        /// <summary>
		/// The UpdateModuleOrder method update Modules Order.<br />
		/// UpdateModuleOrder Stored Procedure
		/// </summary>
        public void UpdateModuleOrder(int ModuleID, int ModuleOrder, string pane)
        {
            using (SqlCommand myCommand = new SqlCommand(strUpdateModuleOrder, myConnection))
            {
                myCommand.CommandType				= CommandType.StoredProcedure;

                AddModuleIDParameters(myCommand,ModuleID);
                
                SqlParameter parameterModuleOrder	= new SqlParameter(StStr.ATModuleOrder, SqlDbType.Int, 4);
                parameterModuleOrder.Value			= ModuleOrder;
                myCommand.Parameters.Add(parameterModuleOrder);
                
                SqlParameter parameterPaneName		= new SqlParameter(StStr.ATPaneName, SqlDbType.NVarChar, 256);
                parameterPaneName.Value				= pane;
                myCommand.Parameters.Add(parameterPaneName);
                
                try
                {

                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB UpdateModuleOrder:{0}, ex : {1}", ModuleID, exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }
            }
        }
        
        public SqlDataReader GetPageModules(int PageID)
        {
            SqlConnection theConnection         =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection		            =   new SqlConnection(Config.ConnectionString);

            SqlCommand myCommand	            =   new SqlCommand(strGetPageModules, theConnection);

            myCommand.CommandType	= CommandType.StoredProcedure;

            AddPageIDParameters(myCommand,PageID);

            SqlDataReader result	            =   null;
            try
            {
                theConnection.Open();
                result				            =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetPageModules ex : {0}", exp),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if (theConnection.State == System.Data.ConnectionState.Open)
                    theConnection.Close();
                throw;
            }
            return result;
        }

        #endregion Instanciated Module 

        #region Utilities

        /// <summary>
        /// Returns for each module in portal a string which could allow to indentify it visually:
        /// adn_Tabs.TabName + '/' + adn_Modules.ModuleTitle + ' (' + adn_GeneralModuleDefinitions.FriendlyName + ')' AS ModTitle, adn_Tabs.TabOrder
        /// 	beware that Shortcut and ShortcutAll modules are excluded from this list
        /// </summary>
		public DataTable GetModulesSinglePortal(int PortalID)
		{
            if (DBTraceSwitch.Sw.TraceVerbose)
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]ModulesDB GetModulesSinglePortal BEG PortalID : {0}", PortalID),DBTraceSwitch.Sw.Info);
			SqlDataAdapter myAdapt				    = new SqlDataAdapter(strGetModulesSinglePortal, myConnection);
			myAdapt.SelectCommand.CommandType		= CommandType.StoredProcedure;
			
            AddPortalIDParameters(myAdapt.SelectCommand,PortalID);
            
			DataTable myDataTable = new DataTable();
			try
			{
				myAdapt.Fill(myDataTable);
                if (DBTraceSwitch.Sw.TraceVerbose)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]ModulesDB GetModulesSinglePortal Table Count: {0}", myDataTable.Rows.Count),DBTraceSwitch.Sw.Info);
			}
			catch (Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModulesSinglePortal PortalID: {0}, ex : {1}", PortalID, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if (myConnection.State == System.Data.ConnectionState.Open)
					myConnection.Close();
			}
			return myDataTable;
        }

        /// <summary>
        /// Similar to GetModulesSinglePortal, version for all portals
        /// Returns all instanciated modules in all portals with an easy to identify title like
        /// 	adn_Portals.PortalAlias + '/' + adn_Tabs.TabName + '/' + adn_Modules.ModuleTitle + ' (' + adn_GeneralModuleDefinitions.FriendlyName + ')'  AS ModuleTitle, PortalAlias, adn_Tabs.TabOrder
        /// 	beware that Shortcut and ShortcutAll modules are excluded from this list
        /// </summary>
        public DataTable GetModulesAllPortals()
        {
            if (DBTraceSwitch.Sw.TraceVerbose)
                Trace.WriteLine("[V]ModulesDB GetModulesAllPortals BEG",DBTraceSwitch.Sw.Info);
            SqlDataAdapter myCommand			=   new SqlDataAdapter(strGetModulesAllPortals, myConnection);
            myCommand.SelectCommand.CommandType =   CommandType.StoredProcedure;
            // Create and Fill the DataSet
            DataTable myDataTable				=   new DataTable();
            try
            {
                myCommand.Fill(myDataTable);
                if (DBTraceSwitch.Sw.TraceVerbose)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]ModulesDB GetModulesAllPortals Table Count: {0}", myDataTable.Rows.Count),DBTraceSwitch.Sw.Info);
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModulesAllPortals ex : {0}", exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }

            // Translate
            string strNoMod                     =   LocalizeHelper.Localize(strNoModule);
            foreach (DataRow dr in myDataTable.Rows)
            {
                if (dr[2].ToString().ToLower(CultureInfo.InvariantCulture) == "unused" )
                {
                    dr[1]                       =   strNoMod;
                    break;
                }
            }

            // Return the datareader
            return myDataTable;
        }

        /// <summary>
        /// Returns for a specific model of module all occurences with a string which could allow to indentify them visually:
        /// adn_Tabs.TabName + '/' + adn_Modules.ModuleTitle + ' (' + adn_GeneralModuleDefinitions.FriendlyName + ')' AS ModTitle, adn_Tabs.TabOrder
        /// If the  Guid is empty, et all the modules instances in portal
        /// 	beware that Shortcut and ShortcutAll modules are excluded from this list
        /// 	also exluding the module occurence send as parameter allowing to exclude the calling module)
        /// </summary>
        public ArrayList FindModulesByGuid(int portalID, Guid guid,int moduleID2Exclude)
        {
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]ModulesDB FindModulesByGuid BEG portalID: {0}, guid: {1}, moduleID2Exclude: {2}", 
                    new object[]{portalID,guid,moduleID2Exclude}),DBTraceSwitch.Sw.Info);

            string proc                                 =   strFindModulesByGuid;
            if ( guid == Guid.Empty )
                proc                                    =   strGetModulesSinglePortal;
            SqlCommand myCommand = new SqlCommand(proc, myConnection);
            {
                myCommand.CommandType				    =   CommandType.StoredProcedure;
                if (guid != Guid.Empty)
                {

                    SqlParameter parameterFriendlyName  =   new SqlParameter(StStr.ATGuid, SqlDbType.UniqueIdentifier);
                    parameterFriendlyName.Value         =   guid;
                    myCommand.Parameters.Add(parameterFriendlyName);
                }
                
                AddPortalIDParameters(myCommand,portalID);

                ArrayList modList					    =   new ArrayList();
                SqlDataReader dr					    =   null;
                try
                {
                    myConnection.Open();
                    dr								    =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    ModuleItem m					    =   null;
                    while (dr.Read())
                    {
                        m							    =   new ModuleItem();
                        m.Title                         =   (string)dr["ModTitle"];
                        m.ID						    =   (int)dr[StStr.DBModuleID]; //"ModuleId"
                        if ( m.ID == moduleID2Exclude )
                            continue;
                        modList.Add(m);
                    }
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB FindModulesByGuid  ex : {0}", exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (dr != null)
                        dr.Close();
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }
                if ( DBTraceSwitch.Sw.TraceVerbose )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]ModulesDB FindModulesByGuid END Count: {0}.", modList.Count ),DBTraceSwitch.Sw.Info);
                return modList;
            }
        }
        /// <summary>
        /// The GetModuleByName method returns a list of all module instances with
        /// the specified Name (it is the ModuleGeneralDefinition FriendlyName) within the Portal. 
        /// It is used to get all instances of a specified module used in a Portal.
        /// </summary>
        public SqlDataReader GetModulesByName(string moduleName, int portalID)
        {
            SqlCommand myCommand				=   new SqlCommand(strGetModulesByName, myConnection);
            myCommand.CommandType				=   CommandType.StoredProcedure;
            
            SqlParameter parameterFriendlyName	=   new SqlParameter(StStr.ATmoduleName, SqlDbType.NVarChar, 128);
            parameterFriendlyName.Value			=   moduleName;
            myCommand.Parameters.Add(parameterFriendlyName);

            AddPortalIDParameters(myCommand,portalID);

            SqlDataReader dr					=   null;
            try
            {
                myConnection.Open();
                dr								=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if (dr != null)
                    dr.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModulesByName: {0}, ex : {1}", moduleName, exp),DBTraceSwitch.Sw.Info);
                throw;
            }

            return dr;
        }
        
        /// <summary>
        /// Is a module of the model module instanciated in the Page
        /// </summary>
        public SqlDataReader GetModulesWithDefIDInTab(int portalID, int pageID,Guid GeneralModDefID)
        {
            SqlCommand myCommand				=   new SqlCommand(strGetModulesWithDefIDInTab, myConnection);
            myCommand.CommandType				=   CommandType.StoredProcedure;
            
            AddPortalIDParameters(myCommand,portalID);
            
            AddPageIDParameters(myCommand,pageID);

            SqlParameter parameterModuleID      =   new SqlParameter(StStr.ATGeneralModDefID, SqlDbType.UniqueIdentifier);
            parameterModuleID.Value             =   GeneralModDefID;
            myCommand.Parameters.Add(parameterModuleID);

            SqlDataReader dr					=   null;
            try
            {
                // Open the database connection and execute the command
                myConnection.Open();
                dr								=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if (dr != null)
                    dr.Close();
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModulesWithDefIDInTab PortalID: {0}, GeneralModDefID: {1}, ex : {2}", portalID,GeneralModDefID,exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            return dr;
        }

        /// <summary>
        /// Is the Product2 module model instanciated with this page
        /// </summary>
        public bool ExistModuleProductsInPage(int PageID, int portalID)
        {
            using (SqlCommand myCommand = new SqlCommand(strGetModulesInPage, myConnection))
            {
                myCommand.CommandType					=   CommandType.StoredProcedure;
                
                AddPageIDParameters(myCommand,PageID);

                AddPortalIDParameters(myCommand,portalID);

                SqlDataReader dr						=   null;
                Guid moduleGuid							=   new Guid("{EC24FABD-FB16-4978-8C81-1ADD39792377}");
                bool ret								=   false;
                try
                {
                    myConnection.Open();
                    dr									=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    while (dr.Read() && !ret )
                    {
                        if (moduleGuid.Equals(dr.GetGuid(1)))
                            ret							=   true;
                    }
                }
                catch (Exception exp)
                {
                    if ( DBTraceSwitch.Sw.TraceError )
                        Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB ExistModuleProductsInTab  ex : {0}", exp),DBTraceSwitch.Sw.Info);
                    throw;
                }
                finally
                {
                    if (dr != null)
                        dr.Close();
                    if (myConnection.State == System.Data.ConnectionState.Open)
                        myConnection.Close();
                }
                return ret;
            }
        }

        /// <summary>
        /// Returns a string containing a list of authorizd roles corresponding to the ParameterRol right on the module
        /// </summary>
        public string GetPermissions(int moduleID, string procedureName, string parameterRol)
        {
            SqlCommand myCommand			    =   new SqlCommand(procedureName, myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            AddModuleIDParameters(myCommand,moduleID);

            SqlParameter parameterAccessRoles   =   new SqlParameter(StStr.ATAccessRoles, SqlDbType.NVarChar, 256);
            parameterAccessRoles.Direction	    =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterAccessRoles);

            SqlParameter parameterRoles		    =   new SqlParameter(parameterRol, SqlDbType.NVarChar, 256);
            parameterRoles.Direction		    =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterRoles);
            
			string ret						    =   string.Empty;
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                ret							    =   parameterRoles.Value.ToString();
                if ( SecurityTraceSwitch.Sw.TraceVerbose)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]ModuleDB getPermissions  moduleID: {0}, Proc: {1}, Role: {2}, ret: '{3}'", 
                        new object[] { moduleID, procedureName, parameterRol,ret}),SecurityTraceSwitch.Sw.Info);
            }
            catch (Exception ex)
            {
                if ( SecurityTraceSwitch.Sw.TraceError || DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine(
                        string.Format("[E]ModuleDB getPermissions  moduleID: {0}, Proc: {1}, Role: {2}, ex: {3}", 
                        new object[] { moduleID, procedureName, parameterRol, ex }),DBTraceSwitch.Sw.Info +"-"+SecurityTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if (myConnection.State == System.Data.ConnectionState.Open)
                    myConnection.Close();
            }

            return ret;
        }

        #endregion Utilities

        #region Module Settings

        /// <summary>
        /// Get all settings for a module 
        /// </summary>
        public SqlDataReader GetModuleSettings(int moduleID)
        {
            SqlConnection theConnection		=   myConnection;
            if (myConnection.State == ConnectionState.Open)
                theConnection							=   new SqlConnection(Config.ConnectionString);
            SqlCommand myCommand						=   new SqlCommand(strGetModuleSettings, theConnection);
            myCommand.CommandType						=   CommandType.StoredProcedure;

            AddModuleIDParameters(myCommand,moduleID);

            SqlDataReader result						=   null;
            try
            {
                theConnection.Open();
                result									=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleSettings moduleID: {0}, ex : {1}",moduleID,exp),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if (theConnection.State == System.Data.ConnectionState.Open)
                    theConnection.Close();
                throw;
            }
            return result;
        }

        /// <summary>
        /// Get all settings in same Group for a module 
        /// </summary>
        public SqlDataReader GetModuleSettingsGroup(int moduleID,SettingItemGroup Group)
        {
            SqlConnection theConnection		            =   myConnection;
            if (myConnection.State == ConnectionState.Open)
                theConnection							=   new SqlConnection(Config.ConnectionString);
            SqlCommand myCommand						=   new SqlCommand(strGetModuleSettingsGroup, theConnection);

            myCommand.CommandType						=   CommandType.StoredProcedure;

            AddModuleIDParameters(myCommand,moduleID);

            SqlParameter parameterGroup		            =   new SqlParameter(StStr.ATSettingGroup, SqlDbType.Int);
            parameterGroup.Value			            =   (int)Group;
            myCommand.Parameters.Add(parameterGroup);

            SqlDataReader result						=   null;
            try
            {
                theConnection.Open();
                result									=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleSettingsGroup moduleID: {0}, Group: {1}, ex : {2}",moduleID,Group,exp),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if (theConnection.State == System.Data.ConnectionState.Open)
                    theConnection.Close();
                throw;
            }
            return result;
        }

        public SqlDataReader GetModuleUserSettings(int moduleID, Guid userID) 
		{			            
            SqlConnection theConnection		=   myConnection;
            if (myConnection.State == ConnectionState.Open)
                theConnection				=   new SqlConnection(Config.ConnectionString);
            SqlCommand myCommand            =   new SqlCommand(strGetModuleUserSettings, theConnection);
            myCommand.CommandType           =   CommandType.StoredProcedure;

            AddModuleIDParameters(myCommand,moduleID);

            SqlParameter parameterUserID    =   new SqlParameter(StStr.ATUserID, SqlDbType.UniqueIdentifier);
            parameterUserID.Value           =   userID;
            myCommand.Parameters.Add(parameterUserID);
            
            SqlDataReader result            =   null;
            try
            {
                theConnection.Open();
                result						=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleUserSettings ex : {0}", exp),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if (theConnection.State == ConnectionState.Open)
                    theConnection.Close();
                throw;
            }
            return result;
        }

        public SqlDataReader GetModuleUserSettingsGroup(int moduleID, Guid userID,SettingItemGroup Group) 
		{			            
            SqlConnection theConnection		=   myConnection;
            if (myConnection.State == ConnectionState.Open)
                theConnection				=   new SqlConnection(Config.ConnectionString);
            SqlCommand myCommand            =   new SqlCommand(strGetModuleUserSettingsGroup, theConnection);
            myCommand.CommandType           =   CommandType.StoredProcedure;

            AddModuleIDParameters(myCommand,moduleID);

            SqlParameter parameterUserID    =   new SqlParameter(StStr.ATUserID, SqlDbType.UniqueIdentifier);
            parameterUserID.Value           =   userID;
            myCommand.Parameters.Add(parameterUserID);
            
            SqlParameter parameterGroup		            =   new SqlParameter(StStr.ATSettingGroup, SqlDbType.Int);
            parameterGroup.Value			            =   (int)Group;
            myCommand.Parameters.Add(parameterGroup);

            SqlDataReader result            =   null;
            try
            {
                theConnection.Open();
                result						=   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetModuleUserSettings ex : {0}", exp),DBTraceSwitch.Sw.Info);
                if (result != null)
                    result.Close();
                if (theConnection.State == ConnectionState.Open)
                    theConnection.Close();
                throw;
            }
            return result;
        }
        
        
        
        /// <summary>
        /// The GetSingleSettings Method returns one module specific setting from the database. 
        /// If not found returns string.Empty
        /// </summary>
        public string GetSingleSettings(int xmoduleID,string SettingName)
        {
            SqlConnection theCnx				    =   myConnection;
            if ( theCnx.State == ConnectionState.Open )
                theCnx                              =   new SqlConnection(Config.ConnectionString);
            SqlCommand myCommand				    =   new SqlCommand(strGetModuleSingleSetting, theCnx);
            myCommand.CommandType				    =   CommandType.StoredProcedure;
            
            AddModuleIDParameters(myCommand,xmoduleID);

            SqlParameter parameterSettingName		=   new SqlParameter(StStr.ATSettingName, SqlDbType.NVarChar, 50);
            parameterSettingName.Value				=   SettingName;
            myCommand.Parameters.Add(parameterSettingName);
			
            SqlParameter parameterSettingValue		=   new SqlParameter(StStr.ATSettingValue, SqlDbType.NVarChar, 1500);
            parameterSettingValue.Direction         =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterSettingValue);

            string result                           =   string.Empty;
            try
            {
                theCnx.Open();
                myCommand.ExecuteNonQuery();
                if ( parameterSettingValue.Value != DBNull.Value )
                    result                          =   (string)parameterSettingValue.Value;
            }
            catch (Exception exp)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB GetSingleSettings ModuleID : {0}, SettingName: {1}, ex : {2}",xmoduleID,SettingName,exp),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if (theCnx.State == ConnectionState.Open)
                        theCnx.Close();
            }
            if ( ModuleTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]ModulesDB GetSingleSettings ModuleID: {0}, result: [{1}]",xmoduleID,result),ModuleTraceSwitch.Sw.Info);
            return result;
        }

        /// <summary>
        /// The UpdateModuleSetting Method updates a single module setting 
        /// in the ModuleConfiguration database table.
        /// </summary>
        public void UpdateModuleSetting(int xmoduleID, string key, String value,int Group)
        {
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]ModulesDB UpdateModuleSetting xmoduleID: {0}, key: [{1}], value: [{2}]",
                    xmoduleID,key,value),DBTraceSwitch.Sw.Info);
            SqlConnection theConnection     =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection               =   new SqlConnection(Config.ConnectionString);
            SqlCommand myCommand			=   new SqlCommand(strUpdateModuleSetting, theConnection);
            myCommand.CommandType			=   CommandType.StoredProcedure;

            AddModuleIDParameters(myCommand,xmoduleID);

            SqlParameter parameterKey		=   new SqlParameter(StStr.ATSettingName, SqlDbType.NVarChar, 50);
            parameterKey.Value				=   key.ToUpper();
            myCommand.Parameters.Add(parameterKey);

            SqlParameter parameterValue		=   new SqlParameter(StStr.ATSettingValue, SqlDbType.NVarChar, 1500);
            parameterValue.Value			=   value;
            myCommand.Parameters.Add(parameterValue);

            SqlParameter parameterGroup		=   new SqlParameter(StStr.ATSettingGroup, SqlDbType.Int);
            parameterGroup.Value			=   Group;
            myCommand.Parameters.Add(parameterGroup);

            try
            {
                theConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB UpdateModuleSetting xmoduleID : {0}, key : {1}, ex : {2}", xmoduleID, key, ex),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if ( theConnection.State == System.Data.ConnectionState.Open)
                    theConnection.Close();
            }
            //Invalidate cache
            CurrentCache.Remove(Key.ModuleSettings(xmoduleID));
        }

		/// <summary>
		/// The UpdateUserModuleSetting Method updates a single module settings 
		/// for the current user in the adn_ModuleUserSettings database table.
		/// </summary>
		public void UpdateUserModuleSetting(int moduleID,Guid userID, string key, string value,int Group) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]ModulesDB UpdateUserModuleSetting moduleID: {0}, UserID: {1}, key: [{2}], value: [{3}]",
                    new object[]{moduleID,userID,key,value}),DBTraceSwitch.Sw.Info);
            SqlConnection theConnection     =   myConnection;
            if ( theConnection.State == ConnectionState.Open )
                theConnection               =   new SqlConnection(Config.ConnectionString);

            try
            {
                using (SqlCommand myCommand = new SqlCommand(strUpdateModuleUserSetting, theConnection))
                {
                    myCommand.CommandType           =   CommandType.StoredProcedure;

                    AddModuleIDParameters(myCommand,moduleID);

                    SqlParameter parameterUserID    =   new SqlParameter(StStr.ATUserID, SqlDbType.UniqueIdentifier);
                    parameterUserID.Value           =   userID;
                    myCommand.Parameters.Add(parameterUserID);

                    SqlParameter parameterKey       =   new SqlParameter(StStr.ATSettingName, SqlDbType.NVarChar, 50);
                    parameterKey.Value              =   key.ToUpper();
                    myCommand.Parameters.Add(parameterKey);

                    SqlParameter parameterValue     =   new SqlParameter(StStr.ATSettingValue, SqlDbType.NVarChar, 1500);
                    parameterValue.Value            =   value;
                    myCommand.Parameters.Add(parameterValue);

                    SqlParameter parameterGroup		=   new SqlParameter(StStr.ATSettingGroup, SqlDbType.Int);
                    parameterGroup.Value			=   Group;
                    myCommand.Parameters.Add(parameterGroup);

                    theConnection.Open();
                    myCommand.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                if (GlobalTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]ModulesDB  UpdateUSerModuleSetting moduleID: {0}, ex: {1}", moduleID, ex), GlobalTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if ( theConnection.State != ConnectionState.Closed)
                    theConnection.Close();
            }
        }

        #endregion Module Settings
    }
}
